I love them, you'll love them: connection strings!

PGCon
2022-05-27

Who am I

• Lætitia Avrot
• PostgreSQL Europe board member
• #PostgresWomen co-founder
• EDB Field CTO
• @l_avrot
• mydbanotebook.org

Connection

• Why do we need to connect?
• What do we need to connect?
• Connection strings
• How to make Postgres connection experience better

Connecting

• Client applications
• Chapter VI.II of Postgres documentation

You just need 4 things:
• A host
• A port
• A user
• A database

$ psql -h localhost -p 5433 -U laetitia test

psql (15devel)
Type "help" for help.

test=# \conninfo
You are connected to database "test" as user "laetitia"
on host "localhost" (address "::1") at port "5433".

Database

$ psql postgresql://laetitia@localhost:5433/test

psql (15devel)
Type "help" for help.

test=# \conninfo
You are connected to database "test" as user "laetitia"
on host "localhost" (address "::1") at port "5433".

Database

$ psql "postgresql://laetitia@localhost:5433/test?\
application_name=Laetitia_laptop_psql"

laetitia=# select
application_name from pg_stat_activity where usename='laetitia';
   application_name   
----------------------
 Laetitia_laptop_psql
(1 row)

Database

$ psql "user=laetitia host=localhost port=5433 dbname=test"

psql (15devel)
Type "help" for help.

test=# \conninfo
You are connected to database "test" as user "laetitia"
on host "localhost" (address "::1") at port "5433".

Database

$ psql "user=laetitia host=localhost port=5433 dbname=test
application_name=Laetitia_laptop_psql"

laetitia=# select
application_name from pg_stat_activity where usename='laetitia';
   application_name   
----------------------
 Laetitia_laptop_psql
(1 row)

Service file

$ cat ~/.pg_service.conf
[mydb]
host=localhost
port=5433
user=test
dbname=laetitia

          psql service=mydb
psql (15devel)
Type "help" for help.

laetitia=> \conninfo
You are connected to database "laetitia" as user "test"
on host "localhost" (address "::1") at port "5433".

So, What's the problem?

• -d [conn_string]
• --maintenance-db [conn_string]
• [conn_string]
• documented or not documented
• possible?

Clients without documentation

clusterdb, createdb, dropdb, pg_restore, reindexdb, vacuumdb

Clients that does not support connection strings

createuser, dropuser, pgbench

Clients that support the flag -d (or --dbname)

clusterdb, pg_basebackup, pg_dump, pg_dumpall, pg_isready, pg_receivewal, pg_recvlogical, pg_restore, psql, reindexdb, vacuumdb

Clients that support the flag --maintenance-db

createdb, dropdb, reindexdb, vacuumdb

Clients that support no flag

clusterdb, psql

Clients that uses the flag -d for something else

pgbench, createuser

-d is the debug flag for pgbench

-d is the database creation permission floag for createuser

Different solutions

Solution 1

Document the behaviour everywhere

➕ easy to do

➖ does not solve the inconsistency

Solution 2

Remove useless client applications

clusterdb, createdb, createuser, dropdb, dropuser, reindexdb, vacuumdb

➕ easy to do

➕ sinplifies things

➖ does not solve all the inconsistencies

➖ breaks backward compatibility

Solution 3

Make sure all the tools can use -d

➕ solves all the inconsistencies

➖ does not solve the documentation problem

➖ breaks backward compatibility for pgbench and createuser

Solution 4

Remove all clients and create a new one to rule them all

➕ solves all the inconsistencies

➕ solves the documentation problem

➖ breaks backward compatibility for all client tools

My perfect solution

Combination of Solutions 1, 2, 3 and 4

➕ solves all the inconsistencies

➕ solves the documentation problem

➖ does only simplify things a little...

➖ breaks backward compatibility for pgbench

First, remove useless clients

createdb / dropdb
• Use any SQL client
• create database ...
• drop database ...

First, remove useless clients

createuser / dropuser
• Use any SQL client
• create role ...
• drop role ...

First, remove useless clients

reindexdb
• Use any SQL client
• reindex database ...

First, remove useless clients

vacuumdb / clusterdb
• Use psql
• Write the query to generate the queries
• Use \gexec

Then, implement the flag -d

• Easy for most clients
• Only problem for pgbench
• Only allow --debug for debug

Then, document the behaviour

• Easy to do

Finally, create a new client to rule them all

• pg export to replace pg_dump
• pg import to replace pg_restore
• pg connect to replace psql
• ...

So, what do you think?